package com.tiendung.otalunch;
import java.util.ArrayList;
import java.util.HashMap;

import javax.crypto.Cipher;
import javax.crypto.SecretKey;
import javax.crypto.SecretKeyFactory;
import javax.crypto.spec.DESKeySpec;

import org.apache.commons.codec.binary.Base64;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;

public class OtaLunchDatabase {
	   private static final String PASSPHRASE = "You can never guess me";
	   private static final String DATABASE_NAME = "otalunch.db";
	   private static final int DATABASE_VERSION = 5;
	   private static final String TABLE_NAME_FACEBOOK = "Facebook";
	   private static final String TABLE_NAME_FRIEND = "FacebookFriend";
	   private static final String TABLE_NAME_FOOD = "Food";
	   
	   private Context context;
	   private SQLiteDatabase db;
	   private SQLiteStatement insertCommand;
	   private SQLiteStatement insertFriendCommand;
	   private SQLiteStatement insertFoodCommand;
	   
	   private static final String INSERT_FACEBOOK_ID = "insert into " + TABLE_NAME_FACEBOOK + " (facebook_id, name, hash) values (?, ?, ?)";
	   private static final String INSERT_FRIEND = "insert into " + TABLE_NAME_FRIEND + " (facebook_id, friend_id, name) values (?, ?, ?)";
	   private static final String INSERT_FOOD = "insert into " + TABLE_NAME_FOOD + " (day, month, year, food_name, restaurant, rating) values (?, ?, ?, ?, ?, ?)";
	   	   
	   public OtaLunchDatabase(Context context) {
	      this.context = context;

	      AccessDatabase access = new AccessDatabase(this.context);
	      this.db = access.getWritableDatabase();
	      this.insertCommand = this.db.compileStatement(INSERT_FACEBOOK_ID);
	      this.insertFriendCommand = this.db.compileStatement(INSERT_FRIEND);
	      this.insertFoodCommand = this.db.compileStatement(INSERT_FOOD);
	   }
	   
	   public String encrypt(String input) {
		  DESKeySpec keySpec;
		  try {
				keySpec = new DESKeySpec(PASSPHRASE.getBytes("UTF8"));
				SecretKeyFactory keyFactory = SecretKeyFactory.getInstance("DES");
				SecretKey key = keyFactory.generateSecret(keySpec);
				  
				// ENCODE plainTextPassword String
				byte[] cleartext = input.getBytes("UTF8");      
				  
				Cipher cipher = Cipher.getInstance("DES"); // cipher is not thread safe
				cipher.init(Cipher.ENCRYPT_MODE, key);
				String encrypedPwd = new String(Base64.encodeBase64(cipher.doFinal(cleartext)));		  
				  
				return encrypedPwd;
		  } catch (Exception e) {
				e.printStackTrace();
		  }
		  
		  return null;
	   }
	   
	   public String decrypt(String input) {
		    DESKeySpec keySpec;
		    try {
				keySpec = new DESKeySpec(PASSPHRASE.getBytes("UTF8"));
				SecretKeyFactory keyFactory = SecretKeyFactory.getInstance("DES");
				SecretKey key = keyFactory.generateSecret(keySpec);
				
				// DECODE encryptedPwd String		  
				byte[] encrypedPwdBytes = Base64.decodeBase64(input.getBytes());
				Cipher cipher = Cipher.getInstance("DES");// cipher is not thread safe
				cipher.init(Cipher.DECRYPT_MODE, key);
				byte[] plainTextPwdBytes = (cipher.doFinal(encrypedPwdBytes));
				
				return new String(plainTextPwdBytes);
		    } catch (Exception e) {
				e.printStackTrace();
		  }
		  
		  return null;
		      
	   }
	   
	   // insert Facebook ID field
	   public long insert_facebook_id(String facebook_id, String facebook_name, String hash) {
	      this.insertCommand.bindString(1, encrypt(facebook_id));
	      this.insertCommand.bindString(2, encrypt(facebook_name));
	      this.insertCommand.bindString(3, encrypt(hash));
	      return this.insertCommand.executeInsert();
	   }
	   
	   // remove Facebook ID field
	   public void remove_facebook_id() {
		   this.db.delete(TABLE_NAME_FACEBOOK, null, null);
	   }
	   
	   // query Facebook ID field
	   public ArrayList<String> select_facebook() {
	      ArrayList<String> list = new ArrayList<String>();

	      Cursor cursor = this.db.query(TABLE_NAME_FACEBOOK, new String[] { "facebook_id", "name", "hash" }, 
	        null, null, null, null, null);

	      if (cursor.moveToFirst()) {
	            list.add(cursor.getString(0)); 
	            list.add(cursor.getString(1));
	            list.add(cursor.getString(2));
	      }
	      else
	      {
	    	  list.add(null); 
	          list.add(null);
	          list.add(null);
	      }

	      if (cursor != null && !cursor.isClosed()) {
	         cursor.close();
	      }

	      for (int i = 0; i < list.size(); i++)
	    	  if (list.get(i) != null) list.set(i, decrypt(list.get(i)));
	      return list;
	   }
	   
	// insert friends field
	   public long insert_friend_id(String facebook_id, String friend_id, String friend_name) {
	      this.insertFriendCommand.bindString(1, encrypt(facebook_id));
	      this.insertFriendCommand.bindString(2, encrypt(friend_id));
	      this.insertFriendCommand.bindString(3, encrypt(friend_name));
	      return this.insertFriendCommand.executeInsert();
	   }
	   
	   // remove Friends ID
	   public void remove_friends() {
		   this.db.delete(TABLE_NAME_FRIEND, null, null);
	   }
	   
	   // query Friends ID field
	   public ArrayList<String> select_friends() {
	      ArrayList<String> list = new ArrayList<String>();

	      Cursor cursor = this.db.query(TABLE_NAME_FRIEND, new String[] { "friend_id" }, 
	        null, null, null, null, null);


	      if (cursor.moveToFirst()) 
	      {	    	  
	    	  list.add(cursor.getString(0));
	      }
	      	      
	      while (cursor.moveToNext()) 
	      {   
	    	  list.add(cursor.getString(0));
	      }

	      if (cursor != null && !cursor.isClosed()) {
	         cursor.close();
	      }

	      for (int i = 0; i < list.size(); i++)
	    	  if (list.get(i) != null) list.set(i, decrypt(list.get(i)));
	      return list;
	   }
	   
	   // insert food
	   public long insert_food(int day, int month, int year, String name, String restaurant, int rating) {
	      this.insertFoodCommand.bindLong(1, day);
	      this.insertFoodCommand.bindLong(2, month);
	      this.insertFoodCommand.bindLong(3, year);
	      this.insertFoodCommand.bindString(4, name);
	      this.insertFoodCommand.bindString(5, restaurant);
	      this.insertFoodCommand.bindLong(6, rating);
	      return this.insertFoodCommand.executeInsert();
	   }
	   
	   // remove food
	   public void remove_food() {
		   this.db.delete(TABLE_NAME_FOOD, null, null);
	   }
	   
	   // query food
	   public ArrayList<HashMap<String, String>> select_food(int day, int month, int year) {
	      ArrayList<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();

	      Cursor cursor = this.db.query(TABLE_NAME_FOOD, new String[] { "food_name", "restaurant", "rating" }, 
	        "day = '" + day + "' AND month = '" + month + "' AND year = '" + year + "'", null, null, null, null);

	      if (cursor.moveToFirst()) 
	      {
	    	  HashMap<String, String> hm = new HashMap<String, String>();
	    	  hm.put("name", cursor.getString(0));
	    	  hm.put("restaurant", cursor.getString(1));
	    	  hm.put("rating", cursor.getString(2));
	    	  list.add(hm);
	      }
	      else
	      {
	    	  HashMap<String, String> hm = new HashMap<String, String>();
	    	  hm.put("name", null);
	    	  hm.put("restaurant", null);
	    	  hm.put("rating", null);
	    	  list.add(hm);
	      }
	      
	      while (cursor.moveToNext()) 
	      {   
	    	  HashMap<String, String> hm = new HashMap<String, String>();
	    	  hm.put("name", cursor.getString(0));
	    	  hm.put("restaurant", cursor.getString(1));
	    	  hm.put("rating", cursor.getString(2));
	    	  list.add(hm);
	      }

	      if (cursor != null && !cursor.isClosed()) {
	         cursor.close();
	      }
	      
	      return list;
	   }
	   
	   // SQL Database Helper
	   private static class AccessDatabase extends SQLiteOpenHelper {
		   AccessDatabase(Context context) {
	         super(context, DATABASE_NAME, null, DATABASE_VERSION);
	      }

	      @Override
	      public void onCreate(SQLiteDatabase db) {
	         db.execSQL("CREATE TABLE " + TABLE_NAME_FACEBOOK + "(id INTEGER PRIMARY KEY, facebook_id TEXT, name TEXT, hash TEXT)");
	         db.execSQL("CREATE TABLE " + TABLE_NAME_FRIEND + " (id INTEGER PRIMARY KEY, facebook_id TEXT, friend_id TEXT, name TEXT)");
	         db.execSQL("CREATE TABLE " + TABLE_NAME_FOOD + " (id INTEGER PRIMARY KEY, day INTEGER, month INTEGER, year INTEGER, food_name TEXT, restaurant TEXT, rating INTEGER)");
	         
	      }

	      @Override
	      public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	         db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_FACEBOOK);
	         db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_FRIEND);
	         db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_FOOD);
	         onCreate(db);
	      }
	   }
	}